package cn.luckdou.commons.utils;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Created by 杨 on 2017/9/6. fis) {
 List<Map<String, String>> data = new ArrayList<Map<String, Str
 */
public class ExcelImportUtils {

    /**
     * 创建excel文档，
     *
     * @param list        数据
     * @param keys        list中map的key数组集合
     * @param columnNames excel的列名
     */
    public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i);
            // 在row行上创建一个方格
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }

    /**
     * 创建物资一览表的方法
     * @return
     * @throws IOException
     */
    public static Workbook exportWz() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle cellStyleGenter = wb.createCellStyle();//创建样式  居中
        cellStyleGenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //还要设置字体的对齐方式 以及边框
        HSSFCellStyle cellStyleGenter2 = wb.createCellStyle();//创建样式  居中
        cellStyleGenter2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleGenter2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleGenter2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleGenter2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleGenter2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //还需要创建一个 靠右的样式
        HSSFCellStyle cellStyleRight = wb.createCellStyle();//创建样式  靠右
        cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        //创建sheep页
        Sheet sheet=wb.createSheet("第一个Sheet页");  // 创建第一个Sheet页
        sheet.setColumnWidth((short)0,(short)2838);
        sheet.setColumnWidth((short)1,(short)2838);
        sheet.setColumnWidth((short)2,(short)2838);
        sheet.setColumnWidth((short)3,(short)2838);
        sheet.setColumnWidth((short)4,(short)2838);
        sheet.setColumnWidth((short)5,(short)2838);
        sheet.setColumnWidth((short)6,(short)5350);
        sheet.setColumnWidth((short)7,(short)5350);
        sheet.setColumnWidth((short)8,(short)5350);
        sheet.setColumnWidth((short)9,(short)3050);
        sheet.setColumnWidth((short)10,(short)3050);
        sheet.setColumnWidth((short)11,(short)3050);
        sheet.setColumnWidth((short)12,(short)3050);
        sheet.setColumnWidth((short)13,(short)3050);
        sheet.setColumnWidth((short)14,(short)3050);
        sheet.setColumnWidth((short)15,(short)3050);
        sheet.setColumnWidth((short)16,(short)3050);
        //构造行数
        Row row = sheet.createRow(0); //第一行
        row.setHeight((short)320);
        //设置第一行居中  并且合并单元格
        Cell cell=row.createCell(0);
        cell.setCellValue("XXX项目物资检验一览表（第XXX批）！");
        sheet.addMergedRegion(new CellRangeAddress(
                0, // 起始行
                0, // 结束行
                0, // 其实列
                16  // 结束列
        ));
        cell.setCellStyle(cellStyleGenter);
        row = sheet.createRow(1); //第二行
        row.setHeight((short)320);
        //设置第二行靠右  并且合并单元格
        cell=row.createCell(0);
        String str = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
        cell.setCellValue("制表日期 :"+str);
        sheet.addMergedRegion(new CellRangeAddress(
                1, // 起始行
                1, // 结束行
                0, // 起始列
                16  // 结束列
        ));
        cell.setCellStyle(cellStyleRight);
        // 接下来就是创建表头 第三行
        createHead(cellStyleGenter2,sheet,row,cell);
        //填充内容
        Integer count = createBody(cellStyleGenter2, sheet, row, cell);
        // 接下里就是一个合并大的表格 第五行和第六行整体合并
        row = sheet.createRow(count+1); //第五行
        row.setHeight((short)320);
        row = sheet.createRow(count+2); //第六行
        row.setHeight((short)320);
        sheet.addMergedRegion(new CellRangeAddress(
                count+1, // 起始行
                count+2, // 结束行
                0, // 其实列
                255  // 结束列
        ));
        // 设置第七行的内容
        row = sheet.createRow(count+3); //第七行
        row.setHeight((short)320);
        cell = row.createCell(0);
        cell.setCellStyle(cellStyleGenter);
        cell.setCellValue("项目总承包企业：");
        sheet.addMergedRegion(new CellRangeAddress(
                count+3, // 起始行
                count+3, // 结束行
                0, // 其实列
                1  // 结束列
        ));
        cell = row.createCell(2);
        cell.setCellStyle(cellStyleGenter);
        cell.setCellValue("中国国务院");
        sheet.addMergedRegion(new CellRangeAddress(
                count+3, // 起始行
                count+3, // 结束行
                2, // 其实列
                4  // 结束列
        ));
        cell = row.createCell(5);
        cell.setCellStyle(cellStyleGenter);
        cell.setCellValue("地址：");
        cell = row.createCell(6);
        cell.setCellStyle(cellStyleGenter);
        sheet.addMergedRegion(new CellRangeAddress(
                count+3, // 起始行
                count+3, // 结束行
                6, // 其实列
                7  // 结束列
        ));
        cell.setCellValue("我也不知道");
        cell = row.createCell(8);
        cell.setCellStyle(cellStyleGenter);
        cell.setCellValue("传真：");
        cell = row.createCell(9);
        cell.setCellStyle(cellStyleGenter);
        cell.setCellValue("1111");
        sheet.addMergedRegion(new CellRangeAddress(
                count+3, // 起始行
                count+3, // 结束行
                9, // 其实列
                10  // 结束列
        ));
        // 生成文件
      return  wb;
    }
    //创建表头的方法
    private static void  createHead(HSSFCellStyle cellStyleGenter2,Sheet sheet,Row row,Cell cell){
        row = sheet.createRow(2); //第三行
        row.setHeight((short)320);
        cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(1);
        cell.setCellValue("品名");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(2);
        cell.setCellValue("HS编码");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(3);
        cell.setCellValue("数量");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(4);
        cell.setCellValue("单位");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(5);
        cell.setCellValue("品牌");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(6);
        cell.setCellValue("规格、技术参数或配置");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(7);
        cell.setCellValue("单价（单位：元）");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(8);
        cell.setCellStyle(cellStyleGenter2);
        cell.setCellValue("金额（单位：元）");
        cell = row.createCell(9);
        cell.setCellStyle(cellStyleGenter2);
        cell.setCellValue("生产厂商");
        cell = row.createCell(10);
        cell.setCellStyle(cellStyleGenter2);
        cell.setCellValue("供货商");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(11);
        cell.setCellValue("供货地");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(12);
        cell.setCellValue("供货联系电话");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(13);
        cell.setCellValue("供货联系人");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(14);
        cell.setCellValue("出厂日期");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(15);
        cell.setCellValue("出口港");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(16);
        cell.setCellValue("检验标准");
        cell.setCellStyle(cellStyleGenter2);
        cell = row.createCell(17);
        cell.setCellValue("备注");
        cell.setCellStyle(cellStyleGenter2);
    }
    //填充内容的方法
    private static  Integer createBody(HSSFCellStyle cellStyleGenter2,Sheet sheet,Row row,Cell cell){
        ArrayList<String> list = new ArrayList<String>();
        Integer count = 0;
        list.add("1");
        list.add("12");
        list.add("123");
        list.add("1234");
        list.add("12345");
        for (int i=0;i<list.size();i++){
            int s = i+3;
            row = sheet.createRow(s); //第四行
            row.setHeight((short)320);
            cell = row.createCell(0);
            cell.setCellValue("第一行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(1);
            cell.setCellValue("第二行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(2);
            cell.setCellValue("第三行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(3);
            cell.setCellValue("第四行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(4);
            cell.setCellValue("第五行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(5);
            cell.setCellValue("第六行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(6);
            cell.setCellValue("第七行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(7);
            cell.setCellValue("第八行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(8);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(9);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(10);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(11);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(12);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(13);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(14);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(15);
            cell.setCellValue("第九行");
            cell.setCellStyle(cellStyleGenter2);
            cell = row.createCell(16);
            cell.setCellValue("第九行11111");
            cell.setCellStyle(cellStyleGenter2);
            count++;
        }

        return count ;
    }

}
